package com.dfbz;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.dfbz.entity.User;
import com.dfbz.mapper.UserMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;

/**
 * @author lscl
 * @version 1.0
 * @intro:
 */
@SpringBootTest(classes = MyBatisPlusApplication.class)
@RunWith(SpringRunner.class)
public class Demo02_Wrapper基本方法 {

    @Autowired
    private UserMapper userMapper;

    /**
     * QueryMapper的创建
     * SELECT id,name,age,email FROM user
     */
    @Test
    public void test1() {
        // 创建QueryMapper,默认情况下查询所有数据
        QueryWrapper<User> wrapper = Wrappers.query();
        QueryWrapper<User> wrapper2 = new QueryWrapper<>();

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }


    @Test
    public void test2() {
        QueryWrapper<User> wrapper = Wrappers.query();

        // name ='Jack'
//        wrapper.eq("name","Jack");

        /*
        参数1: 是否要进行name条件的拼接
         */
        String name = "Jack";
        wrapper.eq(name != null, "name", name);

        // name != 'Jack'
//        wrapper.ne("name","Jack");

        // age > 20
//        wrapper.gt("age",20);

        // age < 20
//        wrapper.lt("age",20);

        // age=20
//        wrapper.lt("age",20);

        // age between 20 and 24
//        wrapper.between("age",20,24);

        // age not between 20 and 24
//        wrapper.notBetween("age",20,24);

        // name like "%J%"          自动拼接左右的%
//        wrapper.like("name","J");

        // name not like "%J%"
//        wrapper.notLike("name","J");

        // name like "%J"
//        wrapper.likeLeft("name","J");

        // name like 'J%'
//        wrapper.likeRight("name","J");

        // name is null
//        wrapper.isNull("name");

        // name is not null
//        wrapper.isNotNull("name");

        // name in ('Jack','Tom','Jone')
//        wrapper.in("name","Jack","Tom","Jone");

        // name not in ('Jack','Tom','Jone')
//        wrapper.notIn("name","Jack","Tom","Jone");

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }


    /**
     * 子查询
     */
    @Test
    public void test3() {

        //  创建wrapper对象
        QueryWrapper<User> wrapper = Wrappers.query();

        // 相当于: name in (select name from user where age > 21)
        wrapper.inSql("name", "select name from user where age>21");

        // 相当于: name not in (select name from user where age > 21)
//        wrapper.notInSql("name","select name from user where age>21");

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }


    /**
     * 分组
     */
    @Test
    public void test4() {


        //  创建wrapper对象
        QueryWrapper<User> wrapper = Wrappers.query();

    /*
        相当于 select * from user where gropu sex
         这是一个没有意义的分组,分组必须结合查询的字段来体现,后续学QueryWrapper的select方法再介绍
     */
        wrapper.groupBy("sex");

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }


    /**
     * having操作
     */
    @Test
    public void test5() {

        //  创建wrapper对象
        QueryWrapper<User> wrapper = Wrappers.query();

        // group by sex having sex = 0
        wrapper.groupBy("sex");
        wrapper.having("sex", "0");

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }


    /**
     * 排序
     */
    @Test
    public void test6() {
        //  创建wrapper对象
        QueryWrapper<User> wrapper = Wrappers.query();

        /**
         * 参数1: 是否要进行排序 true:是 false: 否
         * 参数2: 是否是Asc排序(升序), true : asc排序, false: desc排序
         * 参数3: 排序的字段
         * SQL: order by age asc;
         */
//        wrapper.orderBy(true,false,"age");

        // order by age asc
//        wrapper.orderByAsc("age");

        // order by age desc
//        wrapper.orderByDesc("age");

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }


    /**
     * and拼接条件
     */
    @Test
    public void test7() {
        //  创建wrapper对象
        QueryWrapper<User> wrapper = Wrappers.query();

    /*
        默认情况下,多条件是以and拼接
        SQL语句: name LIKE "%a%" AND age > 20 AND sex = 0
     */
        wrapper.like("name", "%a%")
                .lt("age", 20)
                .eq("sex", 0);

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }

    /**
     * or拼接条件
     */
    @Test
    public void test8() {
        //  创建wrapper对象
        QueryWrapper<User> wrapper = Wrappers.query();

    /*
        默认情况下,多条件是以and拼接
        SQL语句: name LIKE "%a%" OR age > 20 AND sex = 0
     */
        wrapper.like("name", "%a%")
                .or()
                .lt("age", 20)
                .eq("sex", 0);          // 该条件仍然以AND拼接

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }
}

